﻿using System;
using System.Collections.Generic;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace ListaCasamentoCL
{
    public class Presente
    {
        public int? Idf_Presente { get; set; }
        public string Nme_Presente { get; set; }
        public string Des_Modelo_Presente { get; set; }
        public string Nme_Ofertante { get; set; }

        public void Save()
        {
            List<MySqlParameter> param = new List<MySqlParameter>();
            param.Add(new MySqlParameter("@Idf_Presente", MySqlDbType.Int32));
            param.Add(new MySqlParameter("@Nme_Presente", MySqlDbType.VarChar));
            param.Add(new MySqlParameter("@Des_Modelo_Presente", MySqlDbType.VarChar));
            param.Add(new MySqlParameter("@Nme_Ofertante", MySqlDbType.VarChar));

            param[0].Value = DBNull.Value;
            param[1].Value = DBNull.Value;
            param[2].Value = DBNull.Value;
            param[3].Value = DBNull.Value;

            if (this.Idf_Presente.HasValue)
                param[0].Value = this.Idf_Presente.Value;
            else
                param[0].Value = DBNull.Value;

            param[1].Value = this.Nme_Presente;
            param[2].Value = this.Des_Modelo_Presente;

            if (!string.IsNullOrEmpty(this.Nme_Ofertante))
                param[3].Value = this.Nme_Ofertante;
            else
                param[3].Value = DBNull.Value;
            
            string sQuery = string.Empty;

            if (this.Idf_Presente.HasValue)
            {
                sQuery = @"
                UPDATE presente SET Nme_Presente = @Nme_Presente,
                Des_Modelo_Presente = @Des_Modelo_Presente,
                Nme_Ofertante = @Nme_Ofertante
                WHERE Idf_Presente = @Idf_Presente;
                ";

                DataAccessLayer.ExecuteNonQuery(System.Data.CommandType.Text, sQuery, param);
            }
            else
            {
                sQuery = @"
                INSERT INTO presente (Nme_Presente, Des_Modelo_Presente, Nme_Ofertante)
                VALUES (@Nme_Presente, @Des_Modelo_Presente, @Nme_Ofertante);
                SELECT LAST_INSERT_ID();
                ";

                object id = DataAccessLayer.ExecuteScalar(System.Data.CommandType.Text, sQuery, param);
                this.Idf_Presente = int.Parse(id.ToString());
            }

        }

        public static void Save(int pIdf_Presente, string pNme_Ofertante)
        {
            List<MySqlParameter> param = new List<MySqlParameter>();
            param.Add(new MySqlParameter("@Idf_Presente", MySqlDbType.Int32));
            param.Add(new MySqlParameter("@Nme_Ofertante", MySqlDbType.VarChar));
            param[0].Value = pIdf_Presente;
            param[1].Value = pNme_Ofertante;

            string sQuery = @"UPDATE Presente SET Nme_Ofertante = @Nme_Ofertante WHERE Idf_Presente = @Idf_Presente";

            DataAccessLayer.ExecuteNonQuery(System.Data.CommandType.Text, sQuery, param);
        }

        public static System.Data.DataTable List()
        {
            return DataAccessLayer.ExecuteReaderDs(System.Data.CommandType.Text, "SELECT * FROM Presente", null).Tables[0];
        }

        public static List<Presente> List(int iPage, int iTop)
        {
            List<Presente> ls = new List<Presente>();
            
            List<MySqlParameter> param = new List<MySqlParameter>();
            param.Add(new MySqlParameter("@Page", MySqlDbType.Int32));
            param.Add(new MySqlParameter("@Top", MySqlDbType.Int32));
            param[0].Value = iPage;
            param[1].Value = iTop;

            string sQuery =
            @"CALL sp_ListarPresente(@Page,@Top);";

            using(MySqlDataReader mDr = DataAccessLayer.ExecuteReader(System.Data.CommandType.Text, sQuery, param))
            {
                while (mDr.Read())
                {
                    Presente obj = new Presente();
                    obj.Idf_Presente = mDr.GetInt32("Idf_Presente");
                    obj.Nme_Presente = mDr.GetString("Nme_Presente");
                    obj.Des_Modelo_Presente = mDr.GetString("Des_Modelo_Presente");
                    obj.Nme_Ofertante = mDr["Nme_Ofertante"].Equals(DBNull.Value) ? string.Empty : mDr.GetString("Nme_Ofertante");
                    ls.Add(obj);
                }
            }

            return ls;
        }

        public static void Delete(int id)
        {
            List<MySqlParameter> param = new List<MySqlParameter>();
            param.Add(new MySqlParameter("@ID", MySqlDbType.Int32));
            param[0].Value = id;

            string sQuery = @"DELETE FROM presente WHERE Idf_Presente = @ID";

            DataAccessLayer.ExecuteNonQuery(System.Data.CommandType.Text, sQuery, param);
        }

        public static void RetirarReserva(int id)
        {
            List<MySqlParameter> param = new List<MySqlParameter>();
            param.Add(new MySqlParameter("@Idf_Presente", MySqlDbType.Int32));
            param[0].Value = id;

            string sQuery = @"UPDATE Presente SET Nme_Ofertante = NULL WHERE Idf_Presente = @Idf_Presente";

            DataAccessLayer.ExecuteNonQuery(System.Data.CommandType.Text, sQuery, param);
        }

        public static int Total()
        {
            string sQuery = "SELECT count(*) as total FROM Presente";
            int iTotal = 0;
            using (MySqlDataReader mDr = DataAccessLayer.ExecuteReader(System.Data.CommandType.Text, sQuery, null))
            {
                if (mDr.Read())
                    iTotal = int.Parse(mDr["total"].ToString());
            }

            return iTotal;
        }
    }

    public class SugestoesPresente
    {
        public int? Idf_Sugestao { get; set; }
        public int Idf_Presente { get; set; }
        public string Nme_Loja { get; set; }
        public string Des_Link { get; set; }

        public static void Delete(int id)
        {
            List<MySqlParameter> param = new List<MySqlParameter>();
            param.Add(new MySqlParameter("@ID", MySqlDbType.Int32));
            param[0].Value = id;

            string sQuery = @"DELETE FROM sugestoes_presente WHERE Idf_Sugestao = @ID";

            DataAccessLayer.ExecuteNonQuery(System.Data.CommandType.Text, sQuery, param);
        }

        public static void DeleteAll(int idPresente)
        {
            List<MySqlParameter> param = new List<MySqlParameter>();
            param.Add(new MySqlParameter("@Idf_Presente", MySqlDbType.Int32));
            param[0].Value = idPresente;

            string sQuery = @"DELETE FROM sugestoes_presente WHERE Idf_Presente = @Idf_Presente";

            DataAccessLayer.ExecuteNonQuery(System.Data.CommandType.Text, sQuery, param);
        }

        public void Save()
        {
            List<MySqlParameter> param = new List<MySqlParameter>();
            param.Add(new MySqlParameter("@Idf_Presente", MySqlDbType.Int32));
            param.Add(new MySqlParameter("@Nme_Loja", MySqlDbType.VarChar));
            param.Add(new MySqlParameter("@Des_Link", MySqlDbType.VarChar));

            param[0].Value = DBNull.Value;
            param[1].Value = DBNull.Value;
            param[2].Value = DBNull.Value;

            param[0].Value = this.Idf_Presente;
            param[1].Value = this.Nme_Loja;
            param[2].Value = this.Des_Link;

            string sQuery = string.Empty;

            sQuery = @"
            INSERT INTO sugestoes_presente (Idf_Presente, Nme_Loja, Des_Link)
            VALUES (@Idf_Presente, @Nme_Loja, @Des_Link);";

            DataAccessLayer.ExecuteNonQuery(System.Data.CommandType.Text, sQuery, param);

        }

        public static List<SugestoesPresente> List(int idPresente)
        {
            List<SugestoesPresente> ls = new List<SugestoesPresente>();

            List<MySqlParameter> param = new List<MySqlParameter>();
            param.Add(new MySqlParameter("@Idf_Presente", MySqlDbType.Int32));
            param[0].Value = idPresente;

            string sQuery = @"SELECT * FROM Sugestoes_Presente WHERE Idf_Presente = @Idf_Presente";

            using (MySqlDataReader mDr = DataAccessLayer.ExecuteReader(System.Data.CommandType.Text, sQuery, param))
            {
                while (mDr.Read())
                {
                    SugestoesPresente obj = new SugestoesPresente();
                    obj.Idf_Presente = mDr.GetInt32("Idf_Presente");
                    obj.Idf_Sugestao = mDr.GetInt32("Idf_Sugestao");
                    obj.Nme_Loja = mDr.GetString("Nme_Loja");
                    obj.Des_Link = mDr.GetString("Des_Link");
                    ls.Add(obj);
                }
            }

            return ls;
        }
    }
}
